package com.qidian.study.dao.impl;

import com.qidian.study.dao.ContentDAO;
import com.qidian.study.pojo.Content;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * @author 戴着假发的程序员
 * @company 起点编程
 */
public class ContentDAOImpl extends BaseDAO implements ContentDAO {
    private Connection con;
    private PreparedStatement pst;
    private ResultSet rs;
    @Override
    public int save(Content c) {
        String sql = "insert into v_content(text1,text2,pic1,pic2,link1,link2,cat_id,create_time,modify_time,status) " +
                " values(?,?,?,?,?,?,?,?,?,?)";
        return executeUpdate(sql,c.getText1(),c.getText2(),c.getPic1(),c.getPic2(),c.getLink1(),c.getLink2(),c.getCatId(),c.getCreateTime(),c.getModifyTime(),c.getStatus());
    }

    @Override
    public int update(Content c) {
        String sql = "update v_content set text1=?,text2=?,pic1=?,pic2=?,link1=?,link2=?,cat_id=?,status=? where content_id=?";
        return executeUpdate(sql,c.getText1(),c.getText2(),c.getPic1(),c.getPic2(),c.getLink1(),c.getLink2(),c.getCatId(),c.getStatus(),c.getContentId());
    }

    @Override
    public Content queryById(Long contentId) {
        Content c = null;
        String sql = "select * from v_content where content_id = ?";
        try{
            con = getCon();
            pst = con.prepareStatement(sql);
            pst.setLong(1,contentId);
            rs = pst.executeQuery();
            if(rs.next()){
                c = new Content();
                c.setCatId(rs.getLong("cat_id"));
                c.setContentId(rs.getLong("content_id"));
                c.setStatus(rs.getInt("status"));
                c.setModifyTime(rs.getString("modify_time"));
                c.setCreateTime(rs.getString("create_time"));
                c.setText2(rs.getString("text2"));
                c.setText1(rs.getString("text1"));
                c.setPic1(rs.getString("pic1"));
                c.setPic2(rs.getString("pic2"));
                c.setLink2(rs.getString("link2"));
                c.setLink1(rs.getString("link1"));
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            closeAll(rs,pst,con);
        }
        return c;
    }

    @Override
    public List<Content> queryAll() {
        List<Content> cs = new ArrayList<>();
        String sql = "select * from v_content";
        try{
            con = getCon();
            pst = con.prepareStatement(sql);
            rs = pst.executeQuery();
            while(rs.next()){
                Content c = new Content();
                c.setCatId(rs.getLong("cat_id"));
                c.setContentId(rs.getLong("content_id"));
                c.setStatus(rs.getInt("status"));
                c.setModifyTime(rs.getString("modify_time"));
                c.setCreateTime(rs.getString("create_time"));
                c.setText2(rs.getString("text2"));
                c.setText1(rs.getString("text1"));
                c.setPic1(rs.getString("pic1"));
                c.setPic2(rs.getString("pic2"));
                c.setLink2(rs.getString("link2"));
                c.setLink1(rs.getString("link1"));
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            closeAll(rs,pst,con);
        }
        return cs;
    }

    @Override
    public List<Content> queryByPage(int page, int size, Content content) {
        List<Content> cs = new ArrayList<>();
        String sql = "select * from v_content where 1 = 1 ";
        if(content!=null){
            if(content.getText1()!=null && !"".equals(content.getText1())){
                sql += " and text1 like '%'" + content.getText1() +"%'";
            }
            if(content.getCatId() != null && content.getCatId()!=0){
                sql += " and cat_id = " + content.getCatId();
            }
        }

        sql += " limit " + (page-1)*size + "," + size;
        try{
            con = getCon();
            pst = con.prepareStatement(sql);
            rs = pst.executeQuery();
            while(rs.next()){
                Content c = new Content();
                c.setCatId(rs.getLong("cat_id"));
                c.setContentId(rs.getLong("content_id"));
                c.setStatus(rs.getInt("status"));
                c.setModifyTime(rs.getString("modify_time"));
                c.setCreateTime(rs.getString("create_time"));
                c.setText2(rs.getString("text2"));
                c.setText1(rs.getString("text1"));
                c.setPic1(rs.getString("pic1"));
                c.setPic2(rs.getString("pic2"));
                c.setLink2(rs.getString("link2"));
                c.setLink1(rs.getString("link1"));
                cs.add(c);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            closeAll(rs,pst,con);
        }
        return cs;
    }

    @Override
    public int count(Content content) {
        int total = 0;
        String sql = "select count(*) from v_content where 1 = 1 ";
        if(content!=null){
            if(content.getText1()!=null && !"".equals(content.getText1())){
                sql += " and text1 like '%'" + content.getText1() +"%'";
            }
            if(content.getCatId() != null && content.getCatId()!=0){
                sql += " and cat_id = " + content.getCatId();
            }
        }

        try{
            con = getCon();
            pst = con.prepareStatement(sql);
            rs = pst.executeQuery();
            if(rs.next()){
                total = rs.getInt(1);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            closeAll(rs,pst,con);
        }
        return total;
    }

    public List<Content> queryByCatId(Long catId){
        List<Content> cs = new ArrayList<>();
        String sql = "select * from v_content where cat_id = ? ";
        try{
            con = getCon();
            pst = con.prepareStatement(sql);
            pst.setLong(1,catId);
            rs = pst.executeQuery();
            while(rs.next()){
                Content c = new Content();
                c.setCatId(rs.getLong("cat_id"));
                c.setContentId(rs.getLong("content_id"));
                c.setStatus(rs.getInt("status"));
                c.setModifyTime(rs.getString("modify_time"));
                c.setCreateTime(rs.getString("create_time"));
                c.setText2(rs.getString("text2"));
                c.setText1(rs.getString("text1"));
                c.setPic1(rs.getString("pic1"));
                c.setPic2(rs.getString("pic2"));
                c.setLink2(rs.getString("link2"));
                c.setLink1(rs.getString("link1"));
                cs.add(c);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            closeAll(rs,pst,con);
        }
        return cs;
    }
}
